// ------------------------------------------------------------------------------------------
// Licensed by Interprise Solutions.
// http://www.InterpriseSolutions.com
// For details on this license please visit  the product homepage at the URL above.
// THE ABOVE NOTICE MUST REMAIN INTACT.
// ------------------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;


namespace InterpriseSuiteEcommerceCommon.InterpriseIntegration.SqlQuery
{
    public class TableSelect : SelectBase, ITable
    {
        private string _tableName;
        private string _alias;
        private bool _withNoLock = true;
        private ITop _top;
        private ColumnCollection _columns = new ColumnCollection();
        private IOrderBy _orderBy;
        private List<ITable> _joinTables = new List<ITable>();
        private ILogicalExpression _qualifier = null;

        public TableSelect(string tableName)
        {
            _tableName = tableName;
        }

        public string TableName
        {
            get { return _tableName; }
        }

        public string Alias
        {
            get { return _alias; }
            set { _alias = value; }
        }

        public bool WithNoLock 
        {
            get { return _withNoLock; }
            set { _withNoLock = value; }
        }

        public virtual ITop Top
        {
            get { return _top; }
            set { _top = value; }
        }

        public ColumnCollection Columns
        {
            get { return _columns; }
        }

        public IOrderBy OrderBy
        {
            get { return _orderBy; }
            set { _orderBy = value; }
        }

        public List<ITable> JoinTables
        {
            get { return _joinTables; }
        }

        public virtual ILogicalExpression Qualifier
        {
            get { return _qualifier; }
            set { _qualifier = value; }
        }

        public IColumn AddColumn(string columnName, Type type)
        {
            IColumn column = new Column(this, columnName, type);
            _columns.Add(column);
            return column;
        }

        public ITable WithColumn(string columnName)
        {
            return WithColumn(columnName, typeof(string));
        }

        public ITable WithColumn(string columnName, Type type)
        {
            IColumn column = AddColumn(columnName, type);
            return this;
        }

        public override string ToSqlSelectString()
        {
            string sql = string.Empty;

            sql += "SELECT ";

            if (null != _top && _top.Count > 0)
            {
                sql += _top.ToSqlSelectString();
            }

            if (_columns.Count > 0)
            {
                for (int ctr = 0; ctr < _columns.Count; ctr++)
                {
                    IColumn column = _columns[ctr];
                    sql += column.ToSqlSelectString();
                    if (ctr + 1 < _columns.Count)
                    {
                        sql += ",";
                    }
                }
            }
            else
            {
                sql += " * ";
            }

            sql += string.Format(" FROM [{0}] {1} {2}", 
                        _tableName, 
                        CommonLogic.IIF(!CommonLogic.IsStringNullOrEmpty(_alias), _alias, string.Empty), 
                        CommonLogic.IIF(this.WithNoLock, " WITH (NOLOCK) ", string.Empty) );

            foreach (ITable joinTable in _joinTables)
            {
                sql += joinTable.ToSqlSelectString();
            }

            // the where clause
            if (null != _qualifier)
            {
                sql += " WHERE ";
                sql += _qualifier.ToSqlSelectString();
            }

            if (null != _orderBy)
            {
                sql += _orderBy.ToSqlSelectString();
            }

            return sql;
        }
    }
}
